package com.dfbz;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.dfbz.entity.User;
import com.dfbz.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import java.util.function.Consumer;

/**
 * @author lscl
 * @version 1.0
 * @intro:
 */
@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo03_Wrapper其他方法 {

    @Autowired
    private UserMapper userMapper;

    /**
     * and方法
     */
    @Test
    public void test1() {
        QueryWrapper<User> wrapper = Wrappers.query();

        // 生成的SQL为: (age < ? AND (sex = ? OR name LIKE ?))
    /*wrapper.lt("age", 20);
    wrapper.and(new Consumer<QueryWrapper<User>>() {
        @Override
        public void accept(QueryWrapper<User> userQueryWrapper) {
            userQueryWrapper.eq("sex", 0)
                    .or()
                    .like("name", "J");
        }
    });*/

        // 生成的SQL为:  (age < ? AND sex = ? OR name LIKE ?)
        wrapper.lt("age", 20)
                .eq("sex", 0)
                .or()
                .like("name", "J");

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    /**
     * func方法
     */
    @Test
    public void test2() {
        QueryWrapper<User> wrapper = Wrappers.query();

        // 生成的SQL语句条件: (age < ? AND name LIKE ? AND sex = ?)
        wrapper.lt("age", 20);
        wrapper.func(new Consumer<QueryWrapper<User>>() {
            @Override
            public void accept(QueryWrapper<User> userQueryWrapper) {

                userQueryWrapper.like("name", "a");
                userQueryWrapper.eq("sex", "0");
            }
        });
        // 等价于:
//        wrapper.lt("age", 20).like("name", "a").eq("sex", "0");

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }

    /**
     * nested方法
     */
    @Test
    public void test3() {
        QueryWrapper<User> wrapper = Wrappers.query();

        // 生成的SQL语句条件为: (id = ? AND (name LIKE ? OR age > ?))
        // nested()等价于and方法()
        wrapper.eq("id", 1);
        wrapper.nested(new Consumer<QueryWrapper<User>>() {
            @Override
            public void accept(QueryWrapper<User> userQueryWrapper) {

                // 默认情况下是用and来拼接多个条件
                userQueryWrapper
                        .like("name", "a")
                        .or()
                        .gt("age", 20);
            }
        });

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    /**
     * apply方法
     */
    @Test
    public void test4() {
        QueryWrapper<User> wrapper = Wrappers.query();

        // SQL: (name like ? and age > ?)
//        wrapper.apply("name like {0} and age > {1}", "%J%", 18);

        // SQL: (date_format(birthday, '%Y-%m-%d') = ?)
        wrapper.apply("date_format(birthday, '%Y-%m-%d') = {0}", "2001-10-04");

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    @Test
    public void test5() {
        QueryWrapper<User> wrapper = Wrappers.query();

        // 无视优化规则直接拼接到 sql 的最后,只能调用一次,多次调用以最后一次为准 有sql注入的风险

        // SQL: name = 'Jone'
//        String name = "Jone";
//        wrapper.last("where name = '" + name + "'");

        // SQL: SELECT id,name,sex,age FROM user where name ='' or 1=1; -- '
        String name = "' or 1=1; -- ";
        wrapper.last("where name ='" + name + "'");         // 出现SQL注入问题

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    /**
     * exists方法
     */
    @Test
    public void test6() {
        QueryWrapper<User> wrapper = Wrappers.query();

        // SQL: (EXISTS (select 1))
        wrapper.exists("select 1");

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }


    /**
     * notExists方法
     */
    @Test
    public void test7() {
        QueryWrapper<User> wrapper = Wrappers.query();

        // SQL: (NOT EXISTS (select 1))
        wrapper.notExists("select 1");

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }
}

